﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace WCSY.DAL
{
    public class wcsy_costsOrderMstService
    {
        public wcsy_costsOrderMstService()
		{}
        #region  Method


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string sysno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from wcsy_costsOrderMst");
            strSql.Append(" where sysno='" + sysno + "' ");
            return DBHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(WCSY.MODEL.wcsy_costsOrderMst MODEL)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (MODEL.sysno != null)
            {
                strSql1.Append("sysno,");
                strSql2.Append("'" + MODEL.sysno + "',");
            }
            if (MODEL.applyPsn != null)
            {
                strSql1.Append("applyPsn,");
                strSql2.Append("'" + MODEL.applyPsn + "',");
            }
            if (MODEL.applyDept != null)
            {
                strSql1.Append("applyDept,");
                strSql2.Append("'" + MODEL.applyDept + "',");
            }
            if (MODEL.costOfDateB != null)
            {
                strSql1.Append("costOfDateB,");
                strSql2.Append("'" + MODEL.costOfDateB + "',");
            }
            if (MODEL.costOfDateE != null)
            {
                strSql1.Append("costOfDateE,");
                strSql2.Append("'" + MODEL.costOfDateE + "',");
            }
            if (MODEL.costUse != null)
            {
                strSql1.Append("costUse,");
                strSql2.Append("'" + MODEL.costUse + "',");
            }
            if (MODEL.remarks != null)
            {
                strSql1.Append("remarks,");
                strSql2.Append("'" + MODEL.remarks + "',");
            }
            if (MODEL.fillpsn != null)
            {
                strSql1.Append("fillpsn,");
                strSql2.Append("'" + MODEL.fillpsn + "',");
            }
            if (MODEL.filldate != null)
            {
                strSql1.Append("filldate,");
                strSql2.Append("'" + MODEL.filldate + "',");
            }
            if (MODEL.authorPsn != null)
            {
                strSql1.Append("authorPsn,");
                strSql2.Append("'" + MODEL.authorPsn + "',");
            }
            if (MODEL.authorDate != null)
            {
                strSql1.Append("authorDate,");
                strSql2.Append("'" + MODEL.authorDate + "',");
            }
            if (MODEL.buSysno != null)
            {
                strSql1.Append("buSysno,");
                strSql2.Append("'" + MODEL.buSysno + "',");
            }
            strSql.Append("insert into wcsy_costsOrderMst(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            return strSql.ToString();
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public string Update(WCSY.MODEL.wcsy_costsOrderMst MODEL)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update wcsy_costsOrderMst set ");
            if (MODEL.applyPsn != null)
            {
                strSql.Append("applyPsn='" + MODEL.applyPsn + "',");
            }
            else
            {
                strSql.Append("applyPsn= null ,");
            }
            if (MODEL.applyDept != null)
            {
                strSql.Append("applyDept='" + MODEL.applyDept + "',");
            }
            else
            {
                strSql.Append("applyDept= null ,");
            }
            if (MODEL.costOfDateB != null)
            {
                strSql.Append("costOfDateB='" + MODEL.costOfDateB + "',");
            }
            else
            {
                strSql.Append("costOfDateB= null ,");
            }
            if (MODEL.costOfDateE != null)
            {
                strSql.Append("costOfDateE='" + MODEL.costOfDateE + "',");
            }
            else
            {
                strSql.Append("costOfDateE= null ,");
            }
            if (MODEL.costUse != null)
            {
                strSql.Append("costUse='" + MODEL.costUse + "',");
            }
            else
            {
                strSql.Append("costUse= null ,");
            }
            if (MODEL.remarks != null)
            {
                strSql.Append("remarks='" + MODEL.remarks + "',");
            }
            else
            {
                strSql.Append("remarks= null ,");
            }
            if (MODEL.fillpsn != null)
            {
                strSql.Append("fillpsn='" + MODEL.fillpsn + "',");
            }
            else
            {
                strSql.Append("fillpsn= null ,");
            }
            if (MODEL.filldate != null)
            {
                strSql.Append("filldate='" + MODEL.filldate + "',");
            }
            else
            {
                strSql.Append("filldate= null ,");
            }
            if (MODEL.authorPsn != null)
            {
                strSql.Append("authorPsn='" + MODEL.authorPsn + "',");
            }
            else
            {
                strSql.Append("authorPsn= null ,");
            }
            if (MODEL.authorDate != null)
            {
                strSql.Append("authorDate='" + MODEL.authorDate + "',");
            }
            else
            {
                strSql.Append("authorDate= null ,");
            }
            if (MODEL.buSysno != null)
            {
                strSql.Append("buSysno='" + MODEL.buSysno + "',");
            }
            else
            {
                strSql.Append("buSysno= null ,");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where sysno='" + MODEL.sysno + "' ");
            return strSql.ToString();
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public string Delete(string sysno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from wcsy_costsOrderMst ");
            strSql.Append(" where sysno='" + sysno + "' ");
            return strSql.ToString();
        }		/// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string sysnolist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from wcsy_costsOrderMst ");
            strSql.Append(" where sysno in (" + sysnolist + ")  ");
            int rows = DBHelper.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public WCSY.MODEL.wcsy_costsOrderMst GetMODEL(string sysno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" sysno,applyPsn,applyDept,costOfDateB,costOfDateE,costUse,remarks,fillpsn,filldate,authorPsn,authorDate,buSysno ");
            strSql.Append(" from wcsy_costsOrderMst ");
            strSql.Append(" where sysno='" + sysno + "' ");
            WCSY.MODEL.wcsy_costsOrderMst MODEL = new WCSY.MODEL.wcsy_costsOrderMst();
            DataSet ds = DBHelper.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["sysno"] != null && ds.Tables[0].Rows[0]["sysno"].ToString() != "")
                {
                    MODEL.sysno = ds.Tables[0].Rows[0]["sysno"].ToString();
                }
                if (ds.Tables[0].Rows[0]["applyPsn"] != null && ds.Tables[0].Rows[0]["applyPsn"].ToString() != "")
                {
                    MODEL.applyPsn = ds.Tables[0].Rows[0]["applyPsn"].ToString();
                }
                if (ds.Tables[0].Rows[0]["applyDept"] != null && ds.Tables[0].Rows[0]["applyDept"].ToString() != "")
                {
                    MODEL.applyDept = ds.Tables[0].Rows[0]["applyDept"].ToString();
                }
                if (ds.Tables[0].Rows[0]["costOfDateB"] != null && ds.Tables[0].Rows[0]["costOfDateB"].ToString() != "")
                {
                    MODEL.costOfDateB = DateTime.Parse(ds.Tables[0].Rows[0]["costOfDateB"].ToString());
                }
                if (ds.Tables[0].Rows[0]["costOfDateE"] != null && ds.Tables[0].Rows[0]["costOfDateE"].ToString() != "")
                {
                    MODEL.costOfDateE = DateTime.Parse(ds.Tables[0].Rows[0]["costOfDateE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["costUse"] != null && ds.Tables[0].Rows[0]["costUse"].ToString() != "")
                {
                    MODEL.costUse = ds.Tables[0].Rows[0]["costUse"].ToString();
                }
                if (ds.Tables[0].Rows[0]["remarks"] != null && ds.Tables[0].Rows[0]["remarks"].ToString() != "")
                {
                    MODEL.remarks = ds.Tables[0].Rows[0]["remarks"].ToString();
                }
                if (ds.Tables[0].Rows[0]["fillpsn"] != null && ds.Tables[0].Rows[0]["fillpsn"].ToString() != "")
                {
                    MODEL.fillpsn = ds.Tables[0].Rows[0]["fillpsn"].ToString();
                }
                if (ds.Tables[0].Rows[0]["filldate"] != null && ds.Tables[0].Rows[0]["filldate"].ToString() != "")
                {
                    MODEL.filldate = DateTime.Parse(ds.Tables[0].Rows[0]["filldate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["authorPsn"] != null && ds.Tables[0].Rows[0]["authorPsn"].ToString() != "")
                {
                    MODEL.authorPsn = ds.Tables[0].Rows[0]["authorPsn"].ToString();
                }
                if (ds.Tables[0].Rows[0]["authorDate"] != null && ds.Tables[0].Rows[0]["authorDate"].ToString() != "")
                {
                    MODEL.authorDate = DateTime.Parse(ds.Tables[0].Rows[0]["authorDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["buSysno"] != null && ds.Tables[0].Rows[0]["buSysno"].ToString() != "")
                {
                    MODEL.buSysno = ds.Tables[0].Rows[0]["buSysno"].ToString();
                }
                return MODEL;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetAllListByPara(string sysno, string dept, string applypsn, string fillpsn, string filldateB, string filldateE)
        {
            string sql = @"select mst.*,deptinfo.*,mg_user.username,det.locsum from wcsy_costsordermst as mst
            left join fg_deptinfo as deptinfo on mst.applydept=deptinfo.deptno
            left join mg_user on mst.applypsn = mg_user.userid
            left join (select sysno,sum(isnull(applymoney,0)) as locsum from wcsy_costsorderdet group by sysno) as det on mst.sysno = det.sysno";
            sql += " where 1=1 ";
            if (!sysno.Equals(""))
            {
                sql += " and mst.sysno like '%" + sysno + "%'";
            }
            if (!dept.Equals("-1"))
            {
                sql += " and mst.applydept = '" + dept + "'";
            }
            if (!applypsn.Equals(""))
            {
                sql += " and mst.applypsn like '%" + applypsn + "%'";
            }
            if (!fillpsn.Equals(""))
            {
                sql += " and mst.fillpsn like '%" + fillpsn + "%'";
            }
            if (!filldateB.Equals(""))
            {
                sql += " and mst.filldate>='" + filldateB + "'";
            }
            if (!filldateE.Equals(""))
            {
                sql += " and mst.filldate<='" + filldateE + "' ";
            }
            return DBHelper.Query(sql);
        }
        /// <summary>
        /// 双击费用执行汇总查询费用明细表
        /// </summary>
        /// <param name="busysno"></param>
        /// <param name="deptno"></param>
        /// <param name="costsno"></param>
        /// <returns></returns>
        public DataTable GetOrderListByPara(string busysno,string deptno,string costsno) {
            string sql = @"select det.lineid,det.costsname,det.applymoney,det.remarks as detremarks,costs.name,mst.*,deptinfo.* from wcsy_costsorderdet as det
left join wcsy_costsordermst as mst on det.sysno = mst.sysno
left join fg_deptinfo  as deptinfo  on mst.applydept=deptinfo.deptno
left join fg_costs as costs on det.costsname=costs.code";
            sql += " where 1=1 ";
            if(busysno!=null && !busysno.Equals("")){
                sql += " and mst.busysno='"+busysno+"'";
            }
            if(deptno!=null && !deptno.Equals("")){
                sql += " and mst.applydept='"+deptno+"'";
            }
            if (costsno!=null && !costsno.Equals(""))
            {
                sql += " and det.costsname = '"+costsno+"'";
            }
            return DBHelper.GetDataTable(sql);
        }

        /// <summary>
        /// 查询费用明细表
        /// </summary>
        /// <param name="busysno"></param>
        /// <param name="deptno"></param>
        /// <param name="costsno"></param>
        /// <returns></returns>
        public DataTable GetActiovOrderListByPara(string deptno, string costsno, string uyear, string period)
        {
            string sql = @"select det.lineid,det.costsname,det.applymoney,det.remarks as detremarks,costs.name,mst.*,deptinfo.*,bumst.period,bumst.uyear from wcsy_costsorderdet as det
left join wcsy_costsordermst as mst on det.sysno = mst.sysno
left join fg_deptinfo  as deptinfo  on mst.applydept=deptinfo.deptno
left join fg_costs as costs on det.costsname=costs.code
left join fg_initialbumst as bumst on mst.busysno= bumst.sysno";
            sql += " where 1=1 ";
            
            if (!deptno.Equals("-1"))
            {
                sql += " and mst.applydept='" + deptno + "'";
            }
            if (!costsno.Equals("-1"))
            {
                sql += " and det.costsname = '" + costsno + "'";
            }
            if (!uyear.Equals(""))
            {
                sql += " and bumst.uyear = '" + uyear + "' ";
            }
            if (!period.Equals("-1"))
            {
                sql += " and bumst.period = '" + period + "'";
            }
            return DBHelper.GetDataTable(sql);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sysno,applyPsn,applyDept,costOfDateB,costOfDateE,costUse,remarks,fillpsn,filldate,authorPsn,authorDate,buSysno ");
            strSql.Append(" FROM wcsy_costsOrderMst ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DBHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" sysno,applyPsn,applyDept,costOfDateB,costOfDateE,costUse,remarks,fillpsn,filldate,authorPsn,authorDate,buSysno ");
            strSql.Append(" FROM wcsy_costsOrderMst ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DBHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM wcsy_costsOrderMst ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DBHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.sysno desc");
            }
            strSql.Append(")AS Row, T.*  from wcsy_costsOrderMst T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DBHelper.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取部门费用执行汇总表
        /// </summary>
        /// <param name="deptno"></param>
        /// <param name="costsno"></param>
        /// <param name="uyear"></param>
        /// <param name="period"></param>
        /// <returns></returns>
        public DataTable getBuActioveSumReport(string deptno,string costsno,string uyear,string period) {
            string sql = @"select bumst.sysno,bumst.uyear,bumst.period,budet.deptno,budet.deptname,budet.costsno,budet.costsname,budet.bumoney,
            isnull(activebu.applymoney,0) as applymoney
            from fg_initialbudet as budet
            left join fg_initialbumst as bumst on budet.sysno=bumst.sysno
            left join 
            (select mst.busysno,mst.applydept,det.costsname,sum(det.applymoney) as applymoney from wcsy_costsOrderDet as det
			left join wcsy_costsOrderMst as mst on det.sysno = mst.sysno group by mst.busysno,mst.applydept,det.costsname)
            as activeBu on budet.sysno = activebu.busysno and budet.deptno = activebu.applydept and budet.costsno = activebu.costsname
             where 1=1 ";
            if(!deptno.Equals("-1")){
                sql += " and budet.deptno = '"+deptno+"'";
            }
            if(!costsno.Equals("-1")){
                sql += " and budet.costsno = '"+costsno+"'";
            }
            if(!uyear.Equals("")){
                sql += " and bumst.uyear = '"+uyear+"' ";
            }
            if(!period.Equals("-1")){
                sql += " and bumst.period = '"+period+"'";
            }

            return DBHelper.GetDataTable(sql);
        }


        /*
        */

        #endregion  Method

       
    }
}
